Oracle
Querying Overview
Oracle is a relational database which includes extensions to handle JSON and other modern data types. It uses SQL as the query language. The general format of most queries is
SELECT
FROM
WHERE
ORDER BY
A good Oracle SQL starting point is the page at
https://docs.oracle.com/en/database/oracle/oracle-database/23/sqlrf/SQL-Queries-and-Subqueries.html
Handling JSON Data
For JSON interactions a recommended minimum Oracle version number is 23. A reference point for Oracle JSON interactions is at
https://docs.oracle.com/en/database/oracle/oracle-database/23/adjsn/query-json-data.html
Oracle has several JSON oriented functions described at
https://node-oracledb.readthedocs.io/en/latest/user_guide/json_data_type.html
That page has these statements
CREATE TABLE j_purchaseorder (po_document JSON);
INSERT INTO j_purchaseorder (po_document)
VALUES ('{ "userId": 1, "userName": "Chris", "location": "Australia" }' );
Running this query
select * from j_purchaseorder;
returns the answer set shown below
[
{
PO_DOCUMENT: '{"userId":1,"userName":"Chris","location":"Australia"}'
}
]
Running such a query in Qarbine results in the JSON column type values automatically being converted into JSON objects. This format is much more natural to work with than a simple string value. Qarbine provides a variety of “pragmas” to manipulate the answer set objects. See the main Data Source Designer guide for details.
Oracle queries can use dot notation to extract values from JSON objects. A sample query is
SELECT po.po_document.location
FROM CO.j_purchaseorder po
The alias (i.e., ‘po’) is required in this case.
The existence or not of a JSON value can be used in a WHERE clause as whom below.
SELECT po_document
FROM CO.j_purchaseorder
WHERE JSON_EXISTS (po_document, '$.location')
More information can be found at
https://node-oracledb.readthedocs.io/en/latest/user_guide/json_data_type.html#sql-json-path-expressions
Boolean Values
Oracle does not have a direct BOOLEAN data type for columns in tables. However, it is common to emulate boolean behavior using a CHAR, NUMBER, or VARCHAR2 column with boolean values baked on specific numbers or characters (e.g., 1 and 0 for TRUE and FALSE, or 'Y' and 'N').
The Oracle TO_BOOLEAN function is also available. It expects a 0, 1, ‘true’, or ‘false’ value. For details see
https://docs.oracle.com/en/database/oracle/oracle-database/23/sqlrf/to_boolean.html
However, the Oracle node.js module used with this query
select to_boolean(1) from dual
returns the error
{"error":"NJS-010: unsupported data type 2022 in column 1"}
The error occurs because the to_boolean function in Oracle returns a BOOLEAN data type, which is not supported by the Oracle Node.js module (node-oracledb). The module does not currently handle the BOOLEAN data type in SQL queries.
To work around this issue, you can modify your query to return a data type that is supported by the Oracle Node.js module. For example, you can use a CASE statement to convert the BOOLEAN value into a NUMBER or VARCHAR2:
SELECT CASE WHEN 1 = 1 THEN 'TRUE' ELSE 'FALSE' END AS boolean_value
FROM dual
This query will return a VARCHAR2 value ('TRUE' or 'FALSE'), which the Oracle Node.js module can handle without errors.
Qarbine pragmas can also be used to convert answer set values into booleans.
#pragma convertToBoolean TEST
select 'Y' as test
FROM dual
In this case the TEST field values will be set to true if the Oracle returned value is either 1, ‘Y’, or ‘true’. Otherwise a false will be set.
Notice the CSV list to convertToBoolean for Oracle has all UPPER case field names to match the field names returned by Oracle. More details on pragmas can be found in the Data Source Designer guide.
Date and Timestamp Values
Oracle provides TO_DATE and TO_TIMESTAMP functions to convert from strings to appropriate object types. The SQL may also use the syntax below an an alternative to using these functions.
select LAST_NAME,FIRST_NAME,HIRE_DATE
from EMPLOYEES
where HIRE_DATE >= date '2013-01-01' AND HIRE_DATE <= date '2013-12-31'
Note the “date “ prefix in front of the literals. For more information see https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/Literals.html#GUID-8F4B3F82-8821-4071-84D6-FBBA21C05AC1
Vector Searches
Oracle vector search allows you to query data based on semantics, rather than keywords. The VECTOR data type was introduced with the release of Oracle Database 23ai. Oracle provides several functions to compute distances between 2 embedding points.
| Algorithm | Description |
|---|---|
| L1_DISTANCE | Calculates the Manhattan distance between two vectors. It takes two vectors as input and returns the distance between them as a BINARY_DOUBLE |
| L2_DISTANCE | Calculates the Euclidean distance between two vectors. It takes two vectors as input and returns the distance between them as a BINARY_DOUBLE |
| COSINE_ DISTANCE | Calculates the cosine distance between two vectors. It takes two vectors as input and returns the distance between them as a BINARY_DOUBLE. |
| INNER_ PRODUCT | Calculates the inner product of two vectors. It takes two vectors as input and returns the inner product as a BINARY_DOUBLE. INNER_PRODUCT(<expr1>, <expr2>) is equivalent to -1 * VECTOR_DISTANCE(<expr1>, <expr2>, DOT). |
| HAMMING_ DISTANCE | Calculates the hamming distance between two vectors. It takes two vectors as input and returns the distance between them as a BINARY_DOUBLE. |
| JACCARD_ DISTANCE | Calculates the jaccard distance between two vectors. It takes two BINARY vectors as input and returns the distance between them as a BINARY_DOUBLE. |
A discussion example is at
https://docs.oracle.com/en/database/oracle/oracle-database/23/vecse/approximate-search-using-hnsw.html
A sample vector query from that page is shown below.
SELECT name
FROM galaxies
WHERE name <> 'NGC1073'
ORDER BY VECTOR_DISTANCE( embedding, to_vector('[0,1,1,0,0]'), COSINE )
FETCH APPROXIMATE FIRST 3 ROWS ONLY;
The vector value can be dynamically inserted into the query using a Qarbine variable or macro function. A variable example is
ORDER BY VECTOR_DISTANCE( embedding, to_vector(@myVector), COSINE)
Within a query a macro function is enclosed within ‘[! … !]’. An example is
[! string(embedding(@somePhrase, “AI_Assistant_Alias”) ) !]
Using this approach in the query it looks like the following.
SELECT name
FROM galaxies
WHERE name <> 'NGC1073'
ORDER BY VECTOR_DISTANCE(
embedding, to_vector(
[! string(embedding(@somePhrase, “AI_Assistant_Alias”) ) !]
), COSINE )
FETCH APPROXIMATE FIRST 3 ROWS ONLY;
For more details on vector searches see
https://docs.oracle.com/en/database/oracle/oracle-database/23/vecse/overview-ai-vector-search.html
JSON and vector interactions are described at https://docs.oracle.com/en/database/oracle/oracle-database/23/vecse/json-compatibility-vector-data-type.html
A blog discussing using Oracle 23ai vectors is at
https://enesi.no/2024/05/oracle-database-23ai-and-vector-search/
Answer Set Considerations
Using Pragmas for JSON Data
Below are a few of the pragmas from that documentation to consider using with the Oracle JSON data. The full set is in the Data Source Designer guide.
| Pragma Keyword | Description |
|---|---|
| convertToBoolean | Provide a CSV list of fields to convert from values to booleans. The true values are 1, ‘Y’, ‘YES’ or ‘true’. This is done in-place. |
| deleteFields | Provide a CSV list of fields to delete. The arguments may have field paths of up to 2 levels. The first level can be a document or an array of documents. The deleting is done in-place. This is convenient when the result row/document has many fields and you want all just a few of them. Rather than explicitly list the 20 say fields of the 23, just ‘delete’ those 3 from the answer set. |
| pullFieldsUp | Provide a CSV list of object fields to pull their contents up to the first level. The original field is deleted. This can be useful when there are many inner fields that can be part of the first level. Instead of several template formulas like #container.first and #container.last you can simply use #first and #last via the line #pragma pullFieldsUp container |
Troubleshooting
If there is a query which returns unexpected results then first cross reference the results using the standard Oracle querying tools. The Oracle sqlplus tool is a common utility that can be used to run queries outside of Qarbine for comparison purposes. There are many other popular tools with Oracle support as well. Remember to use equivalent connectivity parameters across the connection string, user and password values.
In the Data Source Designer pressing Alt and clicking the run image returns the effective query that would be sent to Oracle. This query has had all variables and macro functions evaluated by this point.
References
Information on Oracle’s SQL compliance can be found at
https://docs.oracle.com/en/database/oracle/oracle-database/23/sqlrf/Oracle-and-Standard-SQL.html